package com.eece496.Database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.eece496.Helpers.GroupInfo;
import com.eece496.Helpers.StudentGradeInfo;

public class StudentGroups extends Database{

	private String groupNumber;
	private int studentID;
	private String courseNumber;
	private String sectionNumber;

	public StudentGroups() {
		tableName = "StudentGroups";
	}

	public StudentGroups(String groupNumber, int studentID, String courseNumber, String sectionNumber) {
		tableName = "StudentGroups";

		this.groupNumber = groupNumber;
		this.studentID = studentID;
		this.courseNumber = courseNumber;
		this.sectionNumber = sectionNumber;
	}
	
	@Override
	public void Insert() throws SQLException {
		Statement stmt = connection.createStatement();
		
	    String sqlString = "INSERT INTO " + tableName + " VALUES (" + 
						AddQuotes(groupNumber) + ", " +
	    					studentID + ", " +
	    					AddQuotes(courseNumber) + ", " +
	    					AddQuotes(sectionNumber) + 
	    					")";
	    
	    stmt.executeUpdate(sqlString);
	        
	    System.out.println(sqlString + " complete");
		
	}

	@Override
	public void Delete(String primaryKey) throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
	public void DeleteStudentFromGroup(int studentID, String courseNumber, String sectionNumber) throws Exception
	{
		Statement stmt = connection.createStatement();
		
		String sqlString = "DELETE FROM " + tableName + " WHERE " +
		" studentID = " + studentID + " and courseNumber= " + AddQuotes(courseNumber) + " and sectionNumber = " + AddQuotes(sectionNumber);
		
		stmt.executeUpdate(sqlString);
	}

	@Override
	public void Display() throws SQLException {
		// TODO Auto-generated method stub
		
	}
	
	public ArrayList<GroupInfo> GetAllGroups(String courseNumber, String sectionNumber) throws SQLException
	{
		ArrayList<GroupInfo> list = new ArrayList<GroupInfo>();
		
		Statement stmt = connection.createStatement();

		String sqlString = "select StudentGroups.groupNumber, StudentGroups.studentID, Student.firstname, " + 
				"Student.lastname from " +
				" StudentGroups inner join Student on StudentGroups.studentID = Student.studentID " +
				"where StudentGroups.courseNumber = " + AddQuotes(courseNumber) + " and StudentGroups.sectionNumber = " +
				AddQuotes(sectionNumber);

		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			GroupInfo info = new GroupInfo();

			info.groupNumber = rs.getString("groupNumber");
			info.studentID = rs.getInt("studentID");
			info.firstName = rs.getString("firstName");
			info.lastName = rs.getString("lastName");
			list.add(info);
		}
		return list;
	}
}
